package com.apes.scm.account.ledger.repository;

import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;
import java.util.Optional;

@Component
public class LedgerDao extends ApesDao {
    public List findLedgerMains(JSONObject param){
        Optional.ofNullable(param.get("COMPANY_ID")).ifPresent(u->{param.put("SQL_COMPANY_ID"," AND A.COMPANY_ID = :COMPANY_ID");});
        Optional.ofNullable(param.get("PARTNER_ID")).ifPresent(u->{param.put("SQL_PARTNER_ID"," AND A.PARTNER_ID = :PARTNER_ID");});
        Optional.ofNullable(param.get("DEPT_ID")).ifPresent(u->{param.put("SQL_DEPT_ID"," AND A.DEPT_ID = :DEPT_ID");});
        String strWhere ="";
        for (String key : param.keySet()) {
            if (key.indexOf("SQL_")>-1){
                strWhere = strWhere + param.getString(key);
            }
        }
        String sSQL = "SELECT A.COMPANY_ID, A.PARTNER_ID, A.DEPT_ID, A.INVENTORY_MODE, A.ACCOUNTING_METHOD,A.LEDGER_ACCOUNT_ID,SUM(A.AMOUNT) AMOUNT,\n" +
                "        '['||COMPANY_ID||']'||(SELECT T.NAME  FROM MAS_PARTY_ROLE P,MAS_PARTY T WHERE P.PARTY_ID=T.ID AND P.ID=A.COMPANY_ID) COMPANY_NAME,\n" +
                "        '['||PARTNER_ID||']'||(SELECT T.NAME  FROM MAS_PARTY_ROLE P,MAS_PARTY T WHERE P.PARTY_ID=T.ID AND P.ID=A.PARTNER_ID) PARTNER_NAME,\n" +
                "        '['||DEPT_ID||']'||(SELECT T.NAME  FROM MAS_DEPT T WHERE T.ID=A.DEPT_ID) DEPT_NAME,\n" +
                "        (SELECT T.NAME  FROM ACC_COOPERATION T WHERE T.ACCOUNTING_METHOD = A.ACCOUNTING_METHOD AND T.INVENTORY_MODE = A.INVENTORY_MODE) COOPERATION\n" +
                "        FROM ACC_lEDGER A,ACC_lEDGER_ACCOUNT B,SOE_BUSINESS_ATTR_VALUE C\n" +
                "        WHERE A.LEDGER_ACCOUNT_ID = B.ID AND B.Usage=C.ID\n" +
                "        AND  C.CODE IN ('unsettled','freeze','provisional') AND AMOUNT <> 0 " +
                strWhere +
                " GROUP BY A.COMPANY_ID, A.PARTNER_ID, A.DEPT_ID, A.INVENTORY_MODE, A.ACCOUNTING_METHOD,A.LEDGER_ACCOUNT_ID ";


        return this.find(sSQL,param);
    }


    public List findLedgerItems(JSONObject param){
        String sSQL = " SELECT B.ACCOUNTING_DATE,B.INCURRED,B.ORIGIN,B.ORIGIN_BUSINESS_TYPE,B.ORIGIN_BUSINESS_TYPE_NAME,\n" +
                "        B.SOURCE,B.SOURCE_BUSINESS_TYPE,B.SOURCE_BUSINESS_TYPE_NAME\n" +
                "        FROM ACC_lEDGER A,ACC_LEDGER_ENTRY B\n" +
                "        WHERE A.ID=B.LEDGER_ID \n" +
                "        AND A.COMPANY_ID = :COMPANY_ID AND A.PARTNER_ID = :PARTNER_ID AND A.DEPT_ID = :DEPT_ID\n" +
                "        AND A.INVENTORY_MODE = :INVENTORY_MODE AND A.ACCOUNTING_METHOD= :ACCOUNTING_METHOD\n" +
                "        AND A.LEDGER_ACCOUNT_ID = :LEDGER_ACCOUNT_ID";

        Optional.ofNullable(param.get("COMPANY_ID")).ifPresent(u->{param.put("test","sfsdfsk");});

        return this.find(sSQL);
    }

    /**
     * 应收账龄分析明细（分页）
     * @param param
     * @return
     */
    public List findAgingReceivableByPage(JSONObject param) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * FROM (");
        findAgingReceivableSql(param, sb);
        sb.append("AND ROWNUM <=(:page *:size) )T_CB WHERE T_CB.rowno >((:page - 1) * :size)") ;
        return this.find(sb.toString(),param);
    }
    /**
     * 应收账龄分析明细
     * @param param
     * @return
     */
    public List findAgingReceivable(JSONObject param) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * FROM (");
        findAgingReceivableSql(param, sb);
        sb.append(")") ;
        return this.find(sb.toString(),param);
    }

    /**
     * 查询Sql
     * @param param
     * @param sb
     */
    private void findAgingReceivableSql(JSONObject param, StringBuilder sb) {
        sb.append( "SELECT CB.*, ROWNUM AS rowno FROM ( ").append(agingReceivableSql());
        if (param.containsKey("COMPANY_ID") && param.get("COMPANY_ID") != null) {
            sb.append(" AND COMPANY_ID= :COMPANY_ID");
        }
        if (param.containsKey("PARTNER_ID") && param.get("PARTNER_ID") != null) {
            sb.append(" AND PARTNER_ID= :PARTNER_ID");
        }
        if (param.containsKey("ACCOUNTING_DATE") && param.get("ACCOUNTING_DATE") != null) {
            sb.append(" AND ACCOUNTING_DATE<= :ACCOUNTING_DATE");
        }
        if (param.containsKey("TYPE") && param.get("TYPE") != null) {
            sb.append(" AND (SELECT T.ROLE_ID FROM MAS_PARTY_ROLE T WHERE T.ID = PARTNER_ID)= :TYPE");
        }
        sb.append(" GROUP BY COMPANY_ID, PARTNER_ID ORDER BY COMPANY_ID, PARTNER_ID" +
                " ) CB WHERE (ENDING_BALANCE<>0 OR SEVEN_DAY_BALANCE<>0 OR EIGHT_DAY_BALANCE<>0 OR THIRTY_DAY_BALANCE<>0 OR SIXTY_DAY_BALANCE<>0\n" +
                "             OR NINETY_DAY_BALANCE<>0 OR HUNDRED_EIGHTY_DAY_BALANCE<>0 OR OUTSIDE_YEAR_BALANCE<>0 OR WITHIN_YEAR_BALANCE<>0) ");
    }

//    /**
//     * 应收账龄分析总页数
//     * @param param
//     * @return
//     */
//    public List<Map> findAgingReceivableCount(JSONObject param) {
//        StringBuilder sb = new StringBuilder();
//        sb.append("SELECT COUNT(1) COUNT FROM ( ").append(agingReceivableSql());
//        if (param.containsKey("COMPANY_ID") && param.get("COMPANY_ID") != null) {
//            sb.append(" AND COMPANY_ID= :COMPANY_ID");
//        }
//        if (param.containsKey("PARTNER_ID") && param.get("PARTNER_ID") != null) {
//            sb.append(" AND PARTNER_ID= :PARTNER_ID");
//        }
//        if (param.containsKey("ACCOUNTING_DATE") && param.get("ACCOUNTING_DATE") != null) {
//            sb.append(" AND ACCOUNTING_DATE<= :ACCOUNTING_DATE");
//        }
//        sb.append(" GROUP BY COMPANY_ID, PARTNER_ID ORDER BY COMPANY_ID, PARTNER_ID  ) WHERE ENDING_BALANCE<>0 OR SEVEN_DAY_BALANCE<>0 OR EIGHT_DAY_BALANCE<>0 OR THIRTY_DAY_BALANCE<>0 OR SIXTY_DAY_BALANCE<>0\n" +
//                "             OR NINETY_DAY_BALANCE<>0 OR HUNDRED_EIGHTY_DAY_BALANCE<>0 OR OUTSIDE_YEAR_BALANCE<>0 OR WITHIN_YEAR_BALANCE<>0 ");
//        return this.find(sb.toString(),param);
//    }

    /**
     * 应收账龄分析sql
     * @return
     */
    public String agingReceivableSql() {
        return "SELECT COMPANY_ID,\n" +
                "       PARTNER_ID,\n" +
                "          '['||COMPANY_ID||']'||(SELECT C.NAME\n" +
                "                    FROM MAS_PARTY C,\n" +
                "                         MAS_PARTY_ROLE D\n" +
                "                    WHERE C.ID = D.PARTY_ID\n" +
                "                      AND D.ID = COMPANY_ID) COMPANY_NAME ,\n" +
                "          '['||PARTNER_ID||']'||(SELECT NAME FROM MAS_PARTY WHERE ID = (SELECT PARTY_ID FROM MAS_PARTY_ROLE WHERE ID = PARTNER_ID)) PARTNER_NAME, " +
                "       SUM(ENDING_BALANCE)             ENDING_BALANCE,\n" +
                "       SUM(SEVEN_DAY_BALANCE)          SEVEN_DAY_BALANCE,\n" +
                "       SUM(EIGHT_DAY_BALANCE)          EIGHT_DAY_BALANCE,\n" +
                "       SUM(THIRTY_DAY_BALANCE)         THIRTY_DAY_BALANCE,\n" +
                "       SUM(SIXTY_DAY_BALANCE)          SIXTY_DAY_BALANCE,\n" +
                "       SUM(NINETY_DAY_BALANCE)         NINETY_DAY_BALANCE,\n" +
                "       SUM(HUNDRED_EIGHTY_DAY_BALANCE) HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "       SUM(WITHIN_YEAR_BALANCE)        WITHIN_YEAR_BALANCE,\n" +
                "       SUM(OUTSIDE_YEAR_BALANCE)       OUTSIDE_YEAR_BALANCE\n" +
                "FROM (\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                B.INCURRED ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                B.INCURRED SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) <= 7\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                B.INCURRED EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND (TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) <= 15 AND TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) >= 8)\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                B.INCURRED THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND (TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) >= 16 AND TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) <= 30)\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                B.INCURRED SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE\n" +
                "                 ,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND (TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) >= 31 AND TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) <= 60)\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                B.INCURRED NINETY_DAY_BALANCE,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND (TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) >= 61 AND TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) <= 90)\n" +
                "\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE,\n" +
                "                B.INCURRED HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND (TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) >= 91 AND TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) <= 180)\n" +
                "\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                B.INCURRED WITHIN_YEAR_BALANCE,\n" +
                "                0          OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND (TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) >= 181 AND TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) <= 365)\n" +
                "\n" +
                "         UNION ALL\n" +
                "         SELECT A.COMPANY_ID,\n" +
                "                 A.PARTNER_ID,\n" +
                "                B.ACCOUNTING_DATE ACCOUNTING_DATE,\n" +
                "                0          ENDING_BALANCE,\n" +
                "                0          SEVEN_DAY_BALANCE,\n" +
                "                0          EIGHT_DAY_BALANCE,\n" +
                "                0          THIRTY_DAY_BALANCE,\n" +
                "                0          SIXTY_DAY_BALANCE,\n" +
                "                0          NINETY_DAY_BALANCE,\n" +
                "                0          HUNDRED_EIGHTY_DAY_BALANCE,\n" +
                "                0          WITHIN_YEAR_BALANCE,\n" +
                "                B.INCURRED OUTSIDE_YEAR_BALANCE\n" +
                "         FROM ACC_LEDGER A\n" +
                "                  LEFT JOIN ACC_LEDGER_ENTRY B ON A.ID = B.LEDGER_ID\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = 'current_unsettled'\n" +
                "           AND (TRUNC(:ACCOUNTING_DATE) - TRUNC(B.ACCOUNTING_DATE) > 365))\n" +
                " WHERE 1=1   " ;
    }
}
